﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using YArchitech.MEP.Electric.Common;
using libxl;

namespace YArchitech.MEP.Electric.CableLay
{
	public class CableLayDatabase
	{
		private CableLayDatabase()
		{
			this.dbAssist = new SQLiteDatabaseAssist(string.Empty);
		}

		internal static CableLayDatabase ThisDb
		{
			get
			{
				return CableLayDatabase.thisDb;
			}
		}

		public void SetDataSource(string dbFile)
		{
			this.dbAssist.ResetDataSource(dbFile);
		}

		public string GetDataSource()
		{
			return this.dbAssist.DataSource;
		}

		public bool IsOpening()
		{
			return this.dbAssist.IsOpened();
		}

		public void SaveProjectDescribe(string describe)
		{
			try
			{
				this.dbAssist.BeginTransaction();
				string sqlStr = string.Format("update ProjectDescribe set Describe='{0}' where ID=1", describe);
				this.dbAssist.ExecuteCommandEx(sqlStr);
				this.dbAssist.CommitTransaction();
			}
			catch (System.Exception)
			{
				MessageBox.Show("设置工程描述失败，请检查工程文件。", "盈建科提示");
				this.dbAssist.RollbackTransaction();
			}
		}

		public List<CableListField> GetProjectFields()
		{
			List<CableListField> list = new List<CableListField>();
			List<CableListField> result;
			try
			{
				string sqlStr = "select * from ProjectField";
				DataTable dataTable = this.dbAssist.ExecuteQuery(sqlStr);
				if (dataTable == null)
				{
					result = list;
				}
				else
				{
					foreach (object obj in dataTable.Rows)
					{
						DataRow dataRow = (DataRow)obj;
						list.Add(new CableListField
						{
							Name = dataRow["Name"].ToString(),
							DbName = dataRow["DbName"].ToString(),
							Title = dataRow["Title"].ToString(),
							Visible = Convert.ToBoolean(dataRow["Visible"]),
							IsForQuery = Convert.ToBoolean(dataRow["IsForQuery"])
						});
					}
					result = list;
				}
			}
			catch (System.Exception)
			{
				MessageBox.Show("读取电缆敷设工程表头字段失败，请检查工程文件。", "盈建科提示");
				result = new List<CableListField>();
			}
			return result;
		}

		public void SaveProjectFields(List<CableListField> fieldList)
		{
			if (fieldList == null || fieldList.Count <= 0)
			{
				return;
			}
			try
			{
				this.dbAssist.BeginTransaction();
				string sqlStr = "delete from ProjectField";
				this.dbAssist.ExecuteCommandEx(sqlStr);
				foreach (CableListField cableListField in fieldList)
				{
					if (!string.IsNullOrWhiteSpace(cableListField.Name) && !string.IsNullOrWhiteSpace(cableListField.DbName))
					{
						sqlStr = string.Format("insert into ProjectField (Name,DbName,Title,Visible,IsForQuery) values ('{0}','{1}','{2}',{3},{4})", new object[]
						{
							cableListField.Name,
							cableListField.DbName,
							cableListField.Title,
							cableListField.Visible ? 1 : 0,
							cableListField.IsForQuery ? 1 : 0
						});
						this.dbAssist.ExecuteCommandEx(sqlStr);
					}
				}
				this.dbAssist.CommitTransaction();
			}
			catch (System.Exception)
			{
				MessageBox.Show("保存电缆敷设工程表头字段设置失败，请检查工程文件。", "盈建科提示");
				this.dbAssist.RollbackTransaction();
			}
		}

		public List<CableListData> GetCableListDatas()
		{
			List<CableListData> list = new List<CableListData>();
			List<CableListData> result;
			try
			{
				string sqlStr = "select * from CableList";
				DataTable dataTable = this.dbAssist.ExecuteQuery(sqlStr);
				if (dataTable == null)
				{
					result = list;
				}
				else
				{
					foreach (object obj in dataTable.Rows)
					{
						DataRow dataRow = (DataRow)obj;
						list.Add(new CableListData
						{
							CableNum = dataRow["CableNum"].ToString(),
							StartNum = dataRow["StartNum"].ToString(),
							EndNum = dataRow["EndNum"].ToString(),
							CableModel = dataRow["CableModel"].ToString(),
							CableSpec = dataRow["CableSpec"].ToString(),
							Length = dataRow["Length"].ToString(),
							LayPath = dataRow["LayPath"].ToString(),
							LayPathIDs = dataRow["LayPathID"].ToString(),
							Remark = dataRow["Remark"].ToString(),
							SheetID = Convert.ToInt32(dataRow["SheetID"]),
							Row = Convert.ToInt32(dataRow["Row"])
						});
					}
					result = list;
				}
			}
			catch (System.Exception)
			{
				MessageBox.Show("读取电缆清册失败，请检查工程文件。", "盈建科提示");
				result = new List<CableListData>();
			}
			return result;
		}

		public void SaveCableListDatas(List<CableListData> dataList)
		{
			if (dataList == null)
			{
				return;
			}
			try
			{
				this.dbAssist.BeginTransaction();
				string text = "delete from CableList";
				this.dbAssist.ExecuteCommandEx(text);
				foreach (CableListData cableListData in dataList)
				{
					if (-1 != cableListData.SheetID && -1 != cableListData.Row)
					{
						text = string.Format("insert into CableList ", Array.Empty<object>());
						text += string.Format("({0},{1},{2},{3},{4},", new object[]
						{
							"CableNum",
							"StartNum",
							"EndNum",
							"CableModel",
							"CableSpec"
						});
						text += string.Format("{0},{1},{2},{3},{4},{5}) ", new object[]
						{
							"Length",
							"LayPath",
							"LayPathID",
							"Remark",
							"SheetID",
							"Row"
						});
						text += string.Format("values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',{9},{10})", new object[]
						{
							cableListData.CableNum,
							cableListData.StartNum,
							cableListData.EndNum,
							cableListData.CableModel,
							cableListData.CableSpec,
							cableListData.Length,
							cableListData.LayPath,
							cableListData.LayPathIDs,
							cableListData.Remark,
							cableListData.SheetID,
							cableListData.Row
						});
						this.dbAssist.ExecuteCommandEx(text);
					}
				}
				this.dbAssist.CommitTransaction();
			}
			catch (System.Exception)
			{
				MessageBox.Show("保存电缆清册失败，请检查工程文件。", "盈建科提示");
				this.dbAssist.RollbackTransaction();
			}
		}

		public void SaveCableDatasToDb(List<ExcelRecord> excelRecords)
		{
			try
			{
				string sqlStr = "select * from ExcelRecord";
				foreach (object obj in this.dbAssist.ExecuteQuery(sqlStr).Rows)
				{
					DataRow dataRow = (DataRow)obj;
					string b = dataRow["SheetName"].ToString();
					string b2 = dataRow["ExcelPath"].ToString();
					foreach (ExcelRecord excelRecord in excelRecords)
					{
						if (excelRecord.sheetName == b && excelRecord.excelPath == b2)
						{
							excelRecord.id = Convert.ToInt32(dataRow["ID"]);
							return;
						}
					}
				}
				this.dbAssist.BeginTransaction();
				foreach (ExcelRecord excelRecord2 in excelRecords)
				{
					string text = string.Format("insert into ExcelRecord (SheetIndex, SheetName, LengthCol, LayPathCol, ExcelPath)", Array.Empty<object>());
					text += string.Format(" values ('{0}','{1}','{2}','{3}','{4}')", new object[]
					{
						excelRecord2.sheetIndex,
						excelRecord2.sheetName,
						excelRecord2.lengthCol,
						excelRecord2.layPathCol,
						excelRecord2.excelPath
					});
					text += ";select last_insert_rowid();";
					int id = -1;
					this.dbAssist.ExecuteScalarEx(text, out id);
					excelRecord2.id = id;
				}
				this.dbAssist.CommitTransaction();
			}
			catch (System.Exception)
			{
				MessageBox.Show("保存电缆数据至数据库失败。", "盈建科提示");
				this.dbAssist.RollbackTransaction();
			}
		}

		public bool BackfillCableDatasToExcel(List<CableListData> cableListDatas)
		{
			string sqlStr = "select * from ExcelRecord";
			DataTable dataTable = this.dbAssist.ExecuteQuery(sqlStr);
			foreach (CableListData cableListData in cableListDatas)
			{
				DataRow dataRow = dataTable.Rows.Find(cableListData.SheetID);
				if (dataRow != null)
				{
					string text = dataRow["ExcelPath"].ToString();
					Book book = new XmlBook();
					book.setKey("Beijing YArchitech Tongxing Technology Co.,Ltd", "windows-282b2c0f08cbe70966b4616ea0i8d7n0");
					try
					{
						book.load(text);
					}
					catch (System.Exception ex)
					{
						if ("libxl: Permission denied." == ex.Message)
						{
							MessageBox.Show("检测到此Excel文件已在外部打开，请先关闭，再重新操作。", "盈建科提示");
						}
						else if ("libxl: No such file or directory." == ex.Message)
						{
							MessageBox.Show(text + "文件不存在。", "盈建科提示");
						}
						return false;
					}
					int index = int.Parse(dataRow["SheetIndex"].ToString());
					Sheet sheet = book.getSheet(index);
					int col = int.Parse(dataRow["LengthCol"].ToString());
					if (cableListData.Length.Trim().Length > 0)
					{
						sheet.writeNum(cableListData.Row, col, Convert.ToDouble(cableListData.Length));
					}
					int col2 = int.Parse(dataRow["LayPathCol"].ToString());
					if (cableListData.LayPath.Trim().Length > 0)
					{
						sheet.writeStr(cableListData.Row, col2, cableListData.LayPath);
					}
					book.save(text);
				}
			}
			return true;
		}

		public List<EquipmentListData> GetEquipmentListDatas(EquipmentType type)
		{
			List<EquipmentListData> list = new List<EquipmentListData>();
			List<EquipmentListData> result;
			try
			{
				string sqlStr = string.Format("select * from EquipmentList where Type={0}", (int)type);
				DataTable dataTable = this.dbAssist.ExecuteQuery(sqlStr);
				if (dataTable == null)
				{
					result = list;
				}
				else
				{
					foreach (object obj in dataTable.Rows)
					{
						DataRow dataRow = (DataRow)obj;
						list.Add(new EquipmentListData
						{
							Number = dataRow["Number"].ToString(),
							Name = dataRow["Name"].ToString(),
							Level = Convert.ToDouble(dataRow["Level"])
						});
					}
					result = list;
				}
			}
			catch (System.Exception)
			{
				MessageBox.Show("读取设备清册失败，请检查工程文件。", "盈建科提示");
				result = new List<EquipmentListData>();
			}
			return result;
		}

		public void SaveEquipmentListDatas(List<EquipmentListData> dataList, EquipmentType type)
		{
			if (dataList == null || dataList.Count <= 0)
			{
				return;
			}
			try
			{
				this.dbAssist.BeginTransaction();
				string sqlStr = string.Format("delete from EquipmentList where Type={0}", (int)type);
				this.dbAssist.ExecuteCommandEx(sqlStr);
				foreach (EquipmentListData equipmentListData in dataList)
				{
					sqlStr = string.Format("insert into EquipmentList (Type,Number,Name,Level) values ({0}, '{1}','{2}','{3}')", new object[]
					{
						(int)type,
						equipmentListData.Number,
						equipmentListData.Name,
						equipmentListData.Level.ToString()
					});
					this.dbAssist.ExecuteCommandEx(sqlStr);
				}
				this.dbAssist.CommitTransaction();
			}
			catch (System.Exception)
			{
				MessageBox.Show("保存设备清册失败，请检查工程文件。", "盈建科提示");
				this.dbAssist.RollbackTransaction();
			}
		}

		private static CableLayDatabase thisDb = new CableLayDatabase();

		private SQLiteDatabaseAssist dbAssist;
	}
}
